/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nxlv2.nomad.accessdb.dao.jdbc;

import com.nxlv2.nomad.accessdb.dao.DAOException;
import com.nxlv2.nomad.accessdb.dao.IDemandeDao;
import com.nxlv2.nomad.accessdb.dao.NoResultException;
import com.nxlv2.nomad.businessobjects.Demande;
import com.nxlv2.nomad.businessobjects.DetailDemande;
import com.nxlv2.nomad.businessobjects.TypeDemande;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Nastasia
 */
public class JdbcDemandeDao implements IDemandeDao {

    private static final Logger logger = LoggerFactory.getLogger(JdbcDemandeDao.class);
    private static final ConnectionManager manager = new ConnectionManager();

    /**
     *
     * @return id
     */
    @Override
    public int getNextId() {
        Connection con = manager.creerConnection();
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli FROM demande ORDER BY idAppli DESC LIMIT 1");
        String request = builder.toString();
        logger.debug("Select nextID {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement", e);
        }
        try {
            ps.executeQuery();
        } catch (SQLException e) {
            logger.error("Can select nextId demande", e);
            throw new DAOException("Can select nextId demande", e);
        }
        int id = 0;
        ResultSet rs;
        try {
            rs = ps.getResultSet();
            while (rs.next()) {
                id = rs.getInt("idAppli");
                break;
            }
        } catch (SQLException e) {
            logger.error("Can not get resultset.", e);
            throw new DAOException("Can not get resultset.", e);

        } finally {
            manager.fermerConnection();
            logger.debug("nextID {}", id + 1);
            return ((id > 0) ? id + 1 : 1);
        }
        
    }

    /**
     *
     * @param demande
     * @throws DAOException
     */
    @Override
    public void saveDemande(Demande demande) throws DAOException {
        if (null == demande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO demande (idAppli, idBase, numDemande, date, dateCreation, idAdresseFacturation, idAdresseLivraison, idAppliTypeDemande) "
                + "VALUES (?, ?, ?, ?, NOW(), ?, ?, ?)");
        String request = builder.toString();
        logger.debug("insert demande {} ", request);
        System.out.println(demande.getIdAppli()+", "+demande.getIdBase()+", "+demande.getNumDemande()+", "+new java.sql.Date(demande.getDate().getTime())
                +", "+demande.getIdAdresseFacturation()+", "+demande.getIdAdresseLivraison()+", "+demande.getTypeDemande().getId());
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, demande.getIdAppli());
            ps.setInt(2, demande.getIdBase());
            ps.setLong(3, demande.getNumDemande());
            ps.setDate(4, new java.sql.Date(demande.getDate().getTime()));
            ps.setInt(5, demande.getIdAdresseFacturation());
            ps.setInt(6, demande.getIdAdresseLivraison());
            ps.setInt(7, demande.getTypeDemande().getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.", e);
            throw new DAOException("Can not populate request with data.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param id
     * @throws DAOException
     */
    @Override
    public void deleteDemande(int id) throws DAOException {
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        builder.append("DELETE FROM demande WHERE idAppli = ?");
        String request = builder.toString();
        logger.debug("Delete demande: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not delete demande with id.");
            throw new DAOException("Can not delete article with id.", e);
        }
        manager.fermerConnection();
    }
    

    /**
     *
     * @param demande
     * @throws DAOException
     */
    @Override
    public void updateDemande(Demande demande) throws DAOException {
        if (null == demande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE demande SET ");
        builder.append("idBase = ?, ");
        builder.append("date = ?, ");
        builder.append("idAdresseFacturation = ?, ");
        builder.append("idAdresseLivraison = ?, ");
        builder.append("valideDemande = ?, ");
        builder.append("dateModification = NOW() ");
        builder.append("WHERE idAppli = ? ");
        builder.append("AND idAppliTypeDemande = ?");

        String request = builder.toString();
        logger.debug("Update demande: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, demande.getIdBase());
            ps.setDate(2, new java.sql.Date(demande.getDate().getTime()));
            ps.setInt(3, demande.getIdAdresseFacturation());
            ps.setInt(4, demande.getIdAdresseLivraison());
            ps.setInt(5, demande.getValideDemande());
            ps.setInt(6, demande.getIdAppli());
            ps.setInt(7, demande.getTypeDemande().getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param id
     * @return List<Demande>
     * @throws DAOException
     */
    @Override
    public List<Demande> findDemandesById(int id) throws DAOException {
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idBase, idAppli, numDemande, valideDemande, date, idAdresseFacturation, idAdresseLivraison, idAppliTypeDemande ");
        builder.append("FROM demande WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("get demande: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.", e);
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.", e);
            throw new DAOException("Can not get resultset.", e);
        }

        List<Demande> demandes = null;
        try {
            demandes = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == demandes || demandes.isEmpty()) {
            throw new NoResultException("No demande found with id " + id);
        }
manager.fermerConnection();
        return demandes;
    }

    /**
     *
     * @param rst
     * @return List<Demande>
     * @throws SQLException
     */
    private List<Demande> map(ResultSet rst) throws SQLException {
        List<Demande> demandes = new ArrayList<>();

        while (rst.next()) {
            Demande demande = new Demande();
            demande.setIdAppli(rst.getInt("idAppli"));
            demande.setIdBase(rst.getInt("idBase"));
            demande.setNumDemande(rst.getLong("numDemande"));
            demande.setValideDemande(rst.getInt("valideDemande"));
            demande.setDate(rst.getDate("date"));
            demande.setIdAdresseFacturation(rst.getInt("idAdresseFacturation"));
            demande.setIdAdresseLivraison(rst.getInt("idAdresseLivraison"));
            demande.setTypeDemande(TypeDemande.getByID(rst.getInt("idAppliTypeDemande")));
            demandes.add(demande);
        }
        return demandes;
    }

    /**
     *
     * @param rst
     * @return List<DetailDemande>
     * @throws SQLException
     */
    private List<DetailDemande> mapDetailDemande(ResultSet rst) throws SQLException {
        List<DetailDemande> detailDemandes = new ArrayList<>();

        while (rst.next()) {
            DetailDemande detailDemande = new DetailDemande();
            detailDemande.setIdAppli(rst.getInt("idAppli"));
            detailDemande.setIdBase(rst.getInt("idBase"));
            detailDemande.setQuantite(rst.getInt("quantite"));
            detailDemande.setTypeDemande(TypeDemande.getByID(rst.getInt("idAppliTypeDemande")));
            detailDemandes.add(detailDemande);
        }
        return detailDemandes;
    }

    /**
     *
     * @param demande
     * @return List<DetailDemande>
     * @throws DAOException
     */
    @Override
    public List<DetailDemande> getDetailDemandesParDemande(Demande demande) throws DAOException {
        if (null == demande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli , idBase, quantite, idAppliTypeDemande ");
        builder.append("FROM detailDemande ");
        builder.append("WHERE idAppliDemande = ? AND idAppliTypeDemande = ? ");

        String request = builder.toString();
        logger.debug("get detaildemande: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setInt(1, demande.getIdAppli());
            ps.setInt(2, demande.getTypeDemande().getId());
            ps.executeQuery();
        } catch (SQLException e) {
            logger.error("Can not select data with id.", e);
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.", e);
            throw new DAOException("Can not get resultset.", e);
        }
        List<DetailDemande> listDetailDemande = null;
        try {
            listDetailDemande = mapDetailDemande(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == listDetailDemande || listDetailDemande.isEmpty()) {
            throw new NoResultException("No detailDemande found with demande : " + demande);
        }
        manager.fermerConnection();
        return listDetailDemande;
    }
    
    /**
     *
     * @param demande
     * @return List<DetailDemande>
     * @throws DAOException
     */
    @Override
    public List<DetailDemande> getDetailDemandesMajParDemande(Demande demande) throws DAOException {
        if (null == demande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT dd.idAppli , dd.idBase, dd.quantite, dd.idAppliTypeDemande ");
        builder.append("FROM detailDemande dd "
                + "LEFT JOIN demande d ON d.idAppli = dd.idAppliDemande "
                + "LEFT JOIN client c ON c.idAppli = d.idAppliClient "
                + "LEFT JOIN utilisateur u ON u.idAppli = c.idAppliUtilisateur ");
        builder.append("WHERE dd.idAppliDemande = ? AND dd.idAppliTypeDemande = ? "
                + "AND (dd.idBase = 0 OR dd.dateModification > u.dateSynchro) "
                + "GROUP BY dd.idAppliTypeDemande");

        String request = builder.toString();
        logger.debug("get detaildemande: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setInt(1, demande.getIdAppli());
            ps.setInt(2, demande.getTypeDemande().getId());
            ps.executeQuery();
        } catch (SQLException e) {
            logger.error("Can not select data with id.", e);
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.", e);
            throw new DAOException("Can not get resultset.", e);
        }
        List<DetailDemande> listDetailDemande = null;
        try {
            listDetailDemande = mapDetailDemande(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

//        if (null == listDetailDemande || listDetailDemande.isEmpty()) {
//            throw new NoResultException("No detailDemande found with demande : " + demande);
//        }
        manager.fermerConnection();
        return listDetailDemande;
    }

    /**
     *
     * @param detailDemande
     * @param demande
     * @throws DAOException
     */
    @Override
    public void lierDetailDemandeADemande(DetailDemande detailDemande, Demande demande) throws DAOException {
        if (null == demande || null == detailDemande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE detaildemande SET ");
        builder.append("idAppliDemande = ?,");
        builder.append("dateModification = NOW() ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Update detaildemande: {}", request);
        logger.debug("demande: {}", demande);
        logger.debug("detail: {}", detailDemande);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, demande.getIdAppli());
            ps.setInt(2, detailDemande.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.", e);
            throw new DAOException("Can not populate request with data.", e);
        }
        manager.fermerConnection();
    }
}